package com.sd.nacosdemo.Util;

import com.sd.nacosdemo.entity.Cultivate;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

/**
 * @author: JAVASM
 * @className:ExcelUtil
 * @description:
 * @data:2021/5/6-20:53
 * @version:
 * @since: jdk 1.8
 */
public class ExcelUtil {

    public static void main(String[] args) throws FileNotFoundException {
        File file = new File("D:\\Download\\员工培养计划.xlsx");
        InputStream in = new FileInputStream(file);
        Cultivate cultivate = new Cultivate();
        List<Map<String, Object>> list = readXlsx(in, 0,cultivate.getClass());
        System.out.println(list);
    }


     public static List<Map<String,Object>> readXlsx(InputStream in,Integer sheetNum,Class clazz){
        Workbook workbook = null;//工作簿,对应整个xlsx文件
        try {
            workbook = WorkbookFactory.create(in);
        } catch (Exception e) {
            return null;
        }
        List list = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(sheetNum);//一个个sheet工作册
        int rows = sheet.getPhysicalNumberOfRows();//得到总行数
        Row head = sheet.getRow(0);//获取第一行
        int cols = head.getPhysicalNumberOfCells();//获取总列数
        String[] keys = new String[cols];
        //读取第一行
        for(int i=0;i<cols;i++){
             Cell keyCell = head.getCell(i);
             String key = keyCell.getStringCellValue();
             keys[i]=key;
         }

        //读取第二行到最后一行数据
        Map<String,Object> rowMap =null;
        for(int i=1;i<rows;i++){
            Row row = sheet.getRow(i);
            rowMap = new HashMap<>();
            for(int j=0;j<cols;j++){
                Cell cell = row.getCell(j);
                CellType type = cell.getCellType();
                Object value=null;
                if(type==CellType.STRING){
                    value = cell.getStringCellValue();
                }else if(type==CellType.NUMERIC){
                    if(DateUtil.isCellDateFormatted(cell)){
                        Date dateCellValue = cell.getDateCellValue();
                        value = cn.hutool.core.date.DateUtil.formatDate(dateCellValue);
                    }else{
                        double t = cell.getNumericCellValue();
                        long intValue = (long)t;
                        if(intValue==t) {
                            value =intValue;
                        } else {
                            value=t;
                        }
                    }
                }
                rowMap.put(keys[j],value);
            }
            list.add(rowMap);
        }
        return list;
    }


    public  static void writeXlsx(List<Map<String,Object>> maps, String[] keys, OutputStream out){
        //xlsx版本excel
        Workbook book = new XSSFWorkbook();
        //创建工作册
        Sheet sheet = book.createSheet();
        int rows = maps.size();
        int cols = keys.length;
        Row head = sheet.createRow(0);
        for (int i = 0;i<cols;i++){
            String value = keys[i];
            Cell cell = head.createCell(i);
            cell.setCellValue(value);
        }
        for (int i=0;i<rows;i++){
            Map<String,Object> data = maps.get(i);
            Row row = sheet.createRow(i + 1);
            for (int j=0;j<keys.length;j++){
                String key = keys[j];
                Object o = data.get(key);
                Cell cell = row.createCell(j);
                if (o!=null) {
                    if (o.getClass() == Date.class) {
                        cell.setCellValue((Date) o);
                    } else {
                        cell.setCellValue(String.valueOf(o));
                    }
                }else {
                    cell.setCellValue("数据为空");
                }
            }
        }
        try {
            book.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
